// ------------------------------------------------------------------------
// 项目名称：Canroc.Net 
// 版权归属：Canroc（https://gitee.com/canroc）
//
// 许可证信息
// Canroc.Net项目主要遵循 Apache 许可证（版本 2.0）进行分发和使用。许可证位于源代码树根目录中的 LICENSE-APACHE 文件。
//
// 使用条款：
// 1.使用本项目应遵守相关法律法规和许可证的要求。
// 2.不得利用本项目从事危害国家安全、扰乱社会秩序、侵犯他人合法权益等法律法规禁止的活动。
// 3.任何基于本项目二次开发而产生的一切法律纠纷和责任，我们不承担任何责任
//
// 免责声明
// 对于因使用本代码而产生的任何直接、间接、偶然、特殊或后果性损害，我们不承担任何责任。
//
// 其他重要信息
// Canroc.Net 项目的版权、商标、专利和其他相关权利均受相应法律法规的保护。
// ------------------------------------------------------------------------

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;
using System.Reflection;
using System.Text.RegularExpressions;

namespace Canroc.Net.Core.Helper;

/// <summary>
///     Open XML 帮助类
/// </summary>
public static partial class OpenXmlHelper
{
    [GeneratedRegex(@"\{\{|\}\}")]
    private static partial Regex TemplateCellOccupyRegex();

    /// <summary>
    ///     读取Excel文件到DataTable
    /// </summary>
    /// <param name="stream">文件流</param>
    /// <returns></returns>
    public static DataTable QueryAsDataTable(Stream stream)
    {
        using var document = SpreadsheetDocument.Open(stream, false);
        var workbookPart = document.WorkbookPart!;
        var worksheetPart = workbookPart.WorksheetParts.First();
        var worksheet = worksheetPart.Worksheet;
        var sheetData = worksheet.GetFirstChild<SheetData>()!;

        var dt = new DataTable();
        var rows = sheetData.Elements<Row>().ToList();

        if (rows.Count == 0) return dt;

        // 构建表头
        var headerRow = rows.First();
        var cells = headerRow.Elements<Cell>().ToList();
        foreach (var cellValue in cells.Select(cell => GetCellValue(cell, workbookPart)))
        {
            dt.Columns.Add(new DataColumn(cellValue));
        }

        // 填充表格数据
        for (var i = 1; i < rows.Count; i++)
        {
            var row = rows[i];
            var dataRow = dt.NewRow();
            var dataCells = row.Elements<Cell>().ToList();

            for (var j = 0; j < dataCells.Count && j < dt.Columns.Count; j++)
            {
                var cellValue = GetCellValue(dataCells[j], workbookPart);
                dataRow[j] = cellValue;
            }

            dt.Rows.Add(dataRow);
        }

        return dt;
    }

    /// <summary>
    ///     生成Excel模板文件
    /// </summary>
    /// <param name="templatePath">模版位置</param>
    /// <param name="list">集合</param>
    /// <returns></returns>
    public static async Task<MemoryStream> GenerateExcelAsync<T>(string templatePath, List<T> list)
    {
        // 读取模板文件到内存流
        var templateBytes = await File.ReadAllBytesAsync(templatePath);
        var memoryStream = new MemoryStream();
        await memoryStream.WriteAsync(templateBytes);
        memoryStream.Position = 0;

        using var document = SpreadsheetDocument.Open(memoryStream, true);
        var workbookPart = document.WorkbookPart!;
        var worksheetPart = workbookPart.WorksheetParts.First();
        var worksheet = worksheetPart.Worksheet;
        var sheetData = worksheet.GetFirstChild<SheetData>()!;

        // 获取最后一行作为模板行
        var rows = sheetData.Elements<Row>().ToList();
        var templateRow = rows.LastOrDefault();

        if (templateRow == null || list.Count == 0)
            return memoryStream;

        var templateRowIndex = templateRow.RowIndex!.Value;
        var templateCells = templateRow.Elements<Cell>().ToList();

        // 记录需要合并的单元格区域
        var mergedRegions = new List<(uint startRow, uint endRow, int startCol, int endCol)>();

        // 当前行索引 - 从模板行开始，不留空行
        var currentRowIndex = templateRowIndex - 1;

        // 处理数据列表
        foreach (var item in list)
        {
            var newAddRowNum = 0;
            currentRowIndex++;

            // 创建新行
            var newRow = new Row { RowIndex = currentRowIndex };

            // 复制模板行的样式
            if (templateRow.Height != null)
                newRow.Height = templateRow.Height;
            if (templateRow.CustomHeight != null)
                newRow.CustomHeight = templateRow.CustomHeight;
            if (templateRow.Hidden != null)
                newRow.Hidden = templateRow.Hidden;
            if (templateRow.Collapsed != null)
                newRow.Collapsed = templateRow.Collapsed;
            if (templateRow.StyleIndex != null)
                newRow.StyleIndex = templateRow.StyleIndex;

            // 填充数据
            for (var j = 0; j < templateCells.Count; j++)
            {
                var templateCell = templateCells[j];
                var cellValue = GetCellValue(templateCell, workbookPart);

                if (string.IsNullOrEmpty(cellValue)) continue;

                // 创建新单元格
                var newCell = CreateCell(templateCell, currentRowIndex, j);

                // 解析模板值
                var templateCellValues = TemplateCellOccupyRegex()
                    .Replace(cellValue, string.Empty)
                    .Split(".")[1..];

                switch (templateCellValues.Length)
                {
                    case <= 1:
                        {
                            // 简单属性
                            var property = typeof(T).GetProperty(templateCellValues[0]);
                            if (property != null && item != null)
                            {
                                var propertyValue = GetPropertyValue(property, item);
                                SetCellValue(newCell, propertyValue);
                            }

                            break;
                        }
                    case 2 when templateCellValues[0].Contains("[]"):
                        {
                            // 集合属性处理
                            var propertyName = templateCellValues[0].Replace("[]", "");
                            var property = typeof(T).GetProperty(propertyName);

                            if (property?.GetValue(item) is IList childrenList)
                            {
                                newAddRowNum = Math.Max(newAddRowNum, childrenList.Count - 1);

                                for (var z = 0; z < childrenList.Count; z++)
                                {
                                    var childProperty = childrenList[z]?.GetType()
                                        .GetProperty(templateCellValues[1]);
                                    var childValue = childProperty != null
                                        ? GetPropertyValue(childProperty, childrenList[z]!)
                                        : "";

                                    if (z == 0)
                                    {
                                        SetCellValue(newCell, childValue);
                                    }
                                    else
                                    {
                                        // 创建子行
                                        var childRowIndex = currentRowIndex + (uint)z;
                                        var childRow = GetOrCreateRow(sheetData, childRowIndex, templateRow);
                                        var childCell = CreateCell(templateCell, childRowIndex, j);
                                        SetCellValue(childCell, childValue);
                                        childRow.Append(childCell);
                                    }
                                }
                            }

                            break;
                        }
                    case 2:
                        {
                            // 嵌套对象属性
                            var property = typeof(T).GetProperty(templateCellValues[0]);
                            if (property != null)
                            {
                                var childEntity = property.GetValue(item);
                                if (childEntity != null)
                                {
                                    var childProperty = childEntity.GetType()
                                        .GetProperty(templateCellValues[1]);
                                    if (childProperty != null)
                                    {
                                        var propertyValue = GetPropertyValue(childProperty, childEntity);
                                        SetCellValue(newCell, propertyValue);
                                    }
                                }
                            }

                            break;
                        }
                }

                newRow.Append(newCell);
            }

            sheetData.InsertBefore(newRow, templateRow);

            // 处理单元格合并
            if (newAddRowNum > 0)
            {
                for (var j = 0; j < templateCells.Count; j++)
                {
                    var templateCell = templateCells[j];
                    var cellValue = GetCellValue(templateCell, workbookPart);

                    if (cellValue.Contains("[]")) continue;
                    // 为非集合列创建空单元格并记录合并区域
                    for (var z = 1; z <= newAddRowNum; z++)
                    {
                        var childRowIndex = currentRowIndex + (uint)z;
                        var childRow = GetOrCreateRow(sheetData, childRowIndex, templateRow);
                        var emptyCell = CreateCell(templateCell, childRowIndex, j);
                        childRow.Append(emptyCell);
                    }

                    mergedRegions.Add((currentRowIndex, currentRowIndex + (uint)newAddRowNum, j, j));
                }
            }

            currentRowIndex += (uint)newAddRowNum;
        }

        // 删除模板行
        templateRow.Remove();

        // 应用单元格合并
        ApplyMergedRegions(worksheet, mergedRegions);

        // 保存更改
        worksheetPart.Worksheet.Save();
        document.Save();

        memoryStream.Position = 0;
        return memoryStream;
    }

    /// <summary>
    ///     获取单元格值
    /// </summary>
    private static string GetCellValue(Cell cell, WorkbookPart workbookPart)
    {
        if (cell.DataType == null || cell.DataType.Value != CellValues.SharedString) return cell.InnerText;
        var sharedStringTablePart = workbookPart.SharedStringTablePart;
        if (sharedStringTablePart?.SharedStringTable == null) return cell.InnerText;
        var sharedStringTable = sharedStringTablePart.SharedStringTable;
        if (int.TryParse(cell.InnerText, out var id) &&
            id < sharedStringTable.Elements<SharedStringItem>().Count())
        {
            return sharedStringTable.Elements<SharedStringItem>().ElementAt(id).InnerText;
        }

        return cell.InnerText;
    }

    /// <summary>
    ///     设置单元格值
    /// </summary>
    private static void SetCellValue(Cell cell, dynamic value)
    {
        cell.CellValue?.Remove();
        cell.InlineString?.Remove();

        switch (value)
        {
            case string strValue when string.IsNullOrEmpty(strValue):
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue("");
                break;
            case string strValue:
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue(strValue);
                break;
            case double or int or float or decimal:
                cell.DataType = CellValues.Number;
                cell.CellValue = new CellValue(Convert.ToDouble(value).ToString(CultureInfo.InvariantCulture));
                break;
            case DateTime dateValue:
                cell.DataType = CellValues.Number;
                cell.CellValue = new CellValue(dateValue.ToOADate().ToString(CultureInfo.InvariantCulture));
                break;
            case bool boolValue:
                cell.DataType = CellValues.Boolean;
                cell.CellValue = new CellValue(boolValue);
                break;
            default:
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue(value.ToString() ?? "");
                break;
        }
    }

    /// <summary>
    ///     创建单元格并复制样式
    /// </summary>
    private static Cell CreateCell(Cell templateCell, uint rowIndex, int columnIndex)
    {
        var columnName = GetColumnName(columnIndex);
        var cellReference = $"{columnName}{rowIndex}";

        var newCell = new Cell
        {
            CellReference = cellReference, StyleIndex = templateCell.StyleIndex, DataType = templateCell.DataType
        };

        // 复制单元格格式属性
        if (templateCell.CellFormula != null)
        {
            newCell.CellFormula = (CellFormula)templateCell.CellFormula.CloneNode(true);
        }

        return newCell;
    }

    /// <summary>
    ///     获取或创建行并复制样式
    /// </summary>
    private static Row GetOrCreateRow(SheetData sheetData, uint rowIndex, Row? templateRow = null)
    {
        var existingRow = sheetData.Elements<Row>()
            .FirstOrDefault(r => r.RowIndex?.Value == rowIndex);

        if (existingRow != null)
            return existingRow;

        var newRow = new Row { RowIndex = rowIndex };

        // 复制模板行的样式
        if (templateRow != null)
        {
            if (templateRow.Height != null)
                newRow.Height = templateRow.Height;
            if (templateRow.CustomHeight != null)
                newRow.CustomHeight = templateRow.CustomHeight;
            if (templateRow.Hidden != null)
                newRow.Hidden = templateRow.Hidden;
            if (templateRow.Collapsed != null)
                newRow.Collapsed = templateRow.Collapsed;
            if (templateRow.StyleIndex != null)
                newRow.StyleIndex = templateRow.StyleIndex;
        }

        // 按行索引插入到正确位置
        var nextRow = sheetData.Elements<Row>()
            .FirstOrDefault(r => r.RowIndex?.Value > rowIndex);

        if (nextRow != null)
            sheetData.InsertBefore(newRow, nextRow);
        else
            sheetData.Append(newRow);

        return newRow;
    }

    /// <summary>
    ///     应用单元格合并
    /// </summary>
    private static void ApplyMergedRegions(Worksheet worksheet,
        List<(uint startRow, uint endRow, int startCol, int endCol)> mergedRegions)
    {
        if (mergedRegions.Count == 0) return;

        var mergeCells = worksheet.GetFirstChild<MergeCells>();
        if (mergeCells == null)
        {
            mergeCells = new MergeCells();
            worksheet.InsertAfter(mergeCells, worksheet.GetFirstChild<SheetData>());
        }

        foreach (var (startRow, endRow, startCol, endCol) in mergedRegions)
        {
            var startColumnName = GetColumnName(startCol);
            var endColumnName = GetColumnName(endCol);
            var reference = $"{startColumnName}{startRow}:{endColumnName}{endRow}";

            var mergeCell = new MergeCell { Reference = reference };
            mergeCells.Append(mergeCell);
        }

        mergeCells.Count = (uint)mergeCells.Elements<MergeCell>().Count();
    }

    /// <summary>
    ///     获取列名（A, B, C, ..., AA, AB, ...）
    /// </summary>
    private static string GetColumnName(int columnIndex)
    {
        var columnName = "";
        while (columnIndex >= 0)
        {
            columnName = (char)('A' + (columnIndex % 26)) + columnName;
            columnIndex = (columnIndex / 26) - 1;
        }

        return columnName;
    }

    /// <summary>
    ///     获取属性值
    /// </summary>
    /// <param name="property">属性</param>
    /// <param name="entity">实体</param>
    /// <returns></returns>
    private static dynamic GetPropertyValue(PropertyInfo property, object entity)
    {
        // 数组
        if (property.PropertyType.IsArray)
        {
            var value = property.GetValue(entity);
            return value is not null ? JsonSerializer.Serialize(value) : "";
        }

        // 枚举
        if (property.PropertyType.IsEnum)
        {
            return property.GetValue(entity) is System.Enum value ? EnumHelper.GetDescription(value) : "";
        }

        // 布尔
        if (property.PropertyType == typeof(bool) || property.PropertyType == typeof(bool?))
        {
            return property.GetValue(entity) is bool value ? value ? "是" : "否" : "";
        }

        // 时间
        if (property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?))
        {
            var value = property.GetValue(entity);
            return value is not null ? (DateTime)value : "";
        }

        // 值类型
        if (property.PropertyType == typeof(short) ||
            property.PropertyType == typeof(short?) ||
            property.PropertyType == typeof(int) ||
            property.PropertyType == typeof(int?) ||
            property.PropertyType == typeof(float) ||
            property.PropertyType == typeof(float?) ||
            property.PropertyType == typeof(double) ||
            property.PropertyType == typeof(double?) ||
            property.PropertyType == typeof(decimal) ||
            property.PropertyType == typeof(decimal?))
        {
            var value = property.GetValue(entity);
            return value is not null ? Convert.ToDouble(value) : "";
        }

        // 字符串类型
        if (property.PropertyType != typeof(string)) return "";
        {
            var value = property.GetValue(entity);
            return value is not null ? value.ToString()! : "";
        }
    }

    /// <summary>
    ///     判断是否为集合
    /// </summary>
    /// <param name="type">类型</param>
    /// <returns></returns>
    public static bool IsListOrEnumerableType(Type type)
    {
        // 排除字符串
        if (type == typeof(string)) return false;
        return typeof(IList).IsAssignableFrom(type) || typeof(IEnumerable).IsAssignableFrom(type);
    }
}